﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using Pixysoft.Framework.XSchEmA.Entity;

using Pixysoft.Framework.XSchEmA;
using Pixysoft.Framework.XSchEmA.Sql;


namespace Pixysoft.Framework.XSchEmA.Aspect
{
    class MySqlSchemaAspect : ISchemaAspect
    {
        private const int default_textlength = 300;

        List<string> dataTypeList;
        List<string> systemNameList;

        public MySqlSchemaAspect()
        {
            dataTypeList = Pixysoft.EnumCoder.ToList<MySqlDataTypeCollection>();
            systemNameList = Pixysoft.EnumCoder.ToList<MySqlKeyWordCollection>();
        }


        DataTypeSchema ISchemaAspect.GetDefaultDataType()
        {
            return new DataTypeSchema(
                MySqlDataTypeCollection.TEXT.ToString(), -1, -1, -1);
        }

        DataTypeSchema ISchemaAspect.AutoCompleteDataType(string datatype)
        {
            if (datatype == null)
                throw new Exception("011001");

            if (!dataTypeList.Contains(datatype))
                return new DataTypeSchema(
                   MySqlDataTypeCollection.TEXT.ToString(), -1, -1, -1);

            MySqlDataTypeCollection datatypeEnum = Pixysoft.EnumCoder.ToEnum<MySqlDataTypeCollection>(datatype);

            switch (datatypeEnum)
            {
                case MySqlDataTypeCollection.TINYINT:
                case MySqlDataTypeCollection.BIT:
                case MySqlDataTypeCollection.BOOL:
                case MySqlDataTypeCollection.BOOLEAN:
                case MySqlDataTypeCollection.SMALLINT:
                case MySqlDataTypeCollection.MEDIUMINT:
                case MySqlDataTypeCollection.INT:
                case MySqlDataTypeCollection.INTEGER:
                case MySqlDataTypeCollection.BIGINT:
                case MySqlDataTypeCollection.FLOAT:
                case MySqlDataTypeCollection.DOUBLE:
                case MySqlDataTypeCollection.DOUBLE_PRECISION:
                case MySqlDataTypeCollection.REAL:
                case MySqlDataTypeCollection.DECIMAL:
                case MySqlDataTypeCollection.DEC:
                case MySqlDataTypeCollection.FIXED:
                case MySqlDataTypeCollection.NUMERIC:
                    return new DataTypeSchema(datatypeEnum.ToString(), -1, 10, -1);

                case MySqlDataTypeCollection.VARBINARY:
                case MySqlDataTypeCollection.BINARY:
                case MySqlDataTypeCollection.DATE:
                case MySqlDataTypeCollection.DATETIME:
                case MySqlDataTypeCollection.TIMESTAMP:
                case MySqlDataTypeCollection.TIME:
                case MySqlDataTypeCollection.YEAR:
                case MySqlDataTypeCollection.TINYBLOB:
                case MySqlDataTypeCollection.TINYTEXT:
                case MySqlDataTypeCollection.BLOB:
                case MySqlDataTypeCollection.TEXT:
                case MySqlDataTypeCollection.MEDIUMBLOB:
                case MySqlDataTypeCollection.MEDIUMTEXT:
                case MySqlDataTypeCollection.LONGBLOB:
                case MySqlDataTypeCollection.LONGTEXT:
                case MySqlDataTypeCollection.ENUM:
                case MySqlDataTypeCollection.SET:
                    return new DataTypeSchema(datatypeEnum.ToString(), -1, -1, -1);

                case MySqlDataTypeCollection.CHAR:
                    return new DataTypeSchema(datatypeEnum.ToString(), 50, -1, -1);
                case MySqlDataTypeCollection.VARCHAR:
                    return new DataTypeSchema(datatypeEnum.ToString(), 200, -1, -1);

                default:
                    return new DataTypeSchema(datatypeEnum.ToString(), -1, -1, -1);
            }
        }

        bool ISchemaAspect.IsValidDataType(DataTypeSchema datatype)
        {
            datatype.Inspection();

            if (!dataTypeList.Contains(datatype.DataType.ToUpper()))
                return false;

            MySqlDataTypeCollection datatypeEnum = Pixysoft.EnumCoder.ToEnum<MySqlDataTypeCollection>(datatype.DataType);

            switch (datatypeEnum)
            {
                case MySqlDataTypeCollection.TINYINT:
                    if (datatype.Precision < 1 || datatype.Precision > 255)
                        return false;
                    break;

                case MySqlDataTypeCollection.BIT:
                case MySqlDataTypeCollection.BOOL:
                case MySqlDataTypeCollection.BOOLEAN:
                    if (datatype.Precision < 1 || datatype.Precision > 64)
                        return false;
                    break;

                case MySqlDataTypeCollection.SMALLINT:
                case MySqlDataTypeCollection.MEDIUMINT:
                case MySqlDataTypeCollection.INT:
                case MySqlDataTypeCollection.INTEGER:
                case MySqlDataTypeCollection.BIGINT:
                    if (datatype.Precision < 1 || datatype.Precision > 255)
                        return false;
                    break;


                case MySqlDataTypeCollection.FLOAT:
                    if (datatype.Precision < 1 || datatype.Precision > 53)
                        return false;
                    if (datatype.Scale != -1 && (datatype.Scale < 1 || datatype.Scale > 30))
                        return false;
                    break;

                case MySqlDataTypeCollection.DOUBLE:
                case MySqlDataTypeCollection.DOUBLE_PRECISION:
                    if (datatype.Precision < 1 || datatype.Precision > 255)
                        return false;
                    if (datatype.Scale != -1 && (datatype.Scale < 1 || datatype.Scale > 30))
                        return false;
                    break;

                case MySqlDataTypeCollection.REAL:
                case MySqlDataTypeCollection.DECIMAL:
                case MySqlDataTypeCollection.DEC:
                case MySqlDataTypeCollection.FIXED:
                case MySqlDataTypeCollection.NUMERIC:
                    if (datatype.Precision < 1 || datatype.Precision > 65)
                        return false;
                    if (datatype.Scale != -1 && (datatype.Scale < 1 || datatype.Scale > 30))
                        return false;
                    break;

                case MySqlDataTypeCollection.CHAR:
                    if (datatype.Length < 1 || datatype.Length > 255)
                        return false;
                    break;
                case MySqlDataTypeCollection.VARCHAR:
                    if (datatype.Length < 1 || datatype.Length > 59516)
                        return false;
                    break;

                default:
                    break;
            }

            return true;
        }

        bool ISchemaAspect.IsUniqueDataType(DataTypeSchema datatype)
        {
            return false;
        }

        string ISchemaAspect.GetDotNetDataType(DataTypeSchema datatypeSchema)
        {
            datatypeSchema.Inspection();

            MySqlDataTypeCollection type = Pixysoft.EnumCoder.ToEnum<MySqlDataTypeCollection>(datatypeSchema.DataType);

            switch (type)
            {
                case MySqlDataTypeCollection.BOOL:
                case MySqlDataTypeCollection.BOOLEAN:
                    return "System.Boolean";

                case MySqlDataTypeCollection.BIT:
                case MySqlDataTypeCollection.YEAR:
                case MySqlDataTypeCollection.TINYINT:
                case MySqlDataTypeCollection.SMALLINT:
                case MySqlDataTypeCollection.MEDIUMINT:
                case MySqlDataTypeCollection.INT:
                case MySqlDataTypeCollection.INTEGER:
                case MySqlDataTypeCollection.BIGINT:
                    return "System.Int64";

                case MySqlDataTypeCollection.FLOAT:
                    return "System.Single";

                case MySqlDataTypeCollection.DOUBLE:
                case MySqlDataTypeCollection.DOUBLE_PRECISION:
                case MySqlDataTypeCollection.REAL:
                case MySqlDataTypeCollection.FIXED:
                case MySqlDataTypeCollection.NUMERIC:
                    return "System.Double";


                case MySqlDataTypeCollection.DEC:
                case MySqlDataTypeCollection.DECIMAL:
                    return "System.Decimal";


                case MySqlDataTypeCollection.DATE:
                case MySqlDataTypeCollection.DATETIME:
                case MySqlDataTypeCollection.TIMESTAMP:
                case MySqlDataTypeCollection.TIME:
                    return "System.DateTime";

                case MySqlDataTypeCollection.VARBINARY:
                case MySqlDataTypeCollection.BINARY:
                case MySqlDataTypeCollection.TINYBLOB:
                case MySqlDataTypeCollection.BLOB:
                case MySqlDataTypeCollection.MEDIUMBLOB:
                case MySqlDataTypeCollection.LONGBLOB:
                    return "System.Byte[]";


                case MySqlDataTypeCollection.ENUM:
                case MySqlDataTypeCollection.SET:
                //2010-07-21 text仍然是binary类型
                //2010-07-22 经过测试 text是string类型
                case MySqlDataTypeCollection.TINYTEXT:
                case MySqlDataTypeCollection.TEXT:
                case MySqlDataTypeCollection.MEDIUMTEXT:
                case MySqlDataTypeCollection.LONGTEXT:
                case MySqlDataTypeCollection.CHAR:
                case MySqlDataTypeCollection.VARCHAR:
                default:
                    return "System.String";
            }
        }

        void ISchemaAspect.TransformToGeneral(DatabaseSchema schema)
        {
            foreach (TableSchema table in schema.Tables)
            {
                foreach (ColumnSchema column in table.Columns)
                {

                    MySqlDataTypeCollection type = Pixysoft.EnumCoder.ToEnum<MySqlDataTypeCollection>(column.DataType.DataType);

                    switch (type)
                    {
                        case MySqlDataTypeCollection.BOOL:
                        case MySqlDataTypeCollection.BOOLEAN:
                            column.DataType = new DataTypeSchema(DotNetDataType.Boolean.ToString(), -1, -1, -1, column.DataType.IsAutoIncrement);
                            break;

                        case MySqlDataTypeCollection.BIT:
                            column.DataType = new DataTypeSchema(DotNetDataType.Int32.ToString(), -1,
                                column.DataType.Precision, -1, column.DataType.IsAutoIncrement);
                            break;

                        case MySqlDataTypeCollection.YEAR:
                        case MySqlDataTypeCollection.TINYINT:
                        case MySqlDataTypeCollection.SMALLINT:
                        case MySqlDataTypeCollection.MEDIUMINT:
                        case MySqlDataTypeCollection.INT:
                        case MySqlDataTypeCollection.INTEGER:
                        case MySqlDataTypeCollection.BIGINT:
                            column.DataType = new DataTypeSchema(DotNetDataType.Int64.ToString(), -1,
                                column.DataType.Precision, column.DataType.Scale, column.DataType.IsAutoIncrement);
                            break;

                        case MySqlDataTypeCollection.FLOAT:


                        case MySqlDataTypeCollection.DOUBLE:
                        case MySqlDataTypeCollection.DOUBLE_PRECISION:
                        case MySqlDataTypeCollection.REAL:
                        case MySqlDataTypeCollection.DECIMAL:
                        case MySqlDataTypeCollection.DEC:
                        case MySqlDataTypeCollection.FIXED:
                        case MySqlDataTypeCollection.NUMERIC:
                            column.DataType = new DataTypeSchema(DotNetDataType.Double.ToString(), -1,
                                column.DataType.Precision, column.DataType.Scale, column.DataType.IsAutoIncrement);
                            break;

                        case MySqlDataTypeCollection.DATE:
                        case MySqlDataTypeCollection.DATETIME:
                        case MySqlDataTypeCollection.TIMESTAMP:
                        case MySqlDataTypeCollection.TIME:
                            column.DataType = new DataTypeSchema(DotNetDataType.DateTime.ToString(), -1, -1, -1, column.DataType.IsAutoIncrement);
                            break;



                        case MySqlDataTypeCollection.VARBINARY:
                        case MySqlDataTypeCollection.BINARY:
                        case MySqlDataTypeCollection.TINYBLOB:
                        case MySqlDataTypeCollection.BLOB:
                        case MySqlDataTypeCollection.MEDIUMBLOB:
                        case MySqlDataTypeCollection.LONGBLOB:

                            column.DataType = new DataTypeSchema(DotNetDataType.Byte.ToString(), -1, -1, -1, column.DataType.IsAutoIncrement);
                            break;



                        //2010-07-21 仍然是byte类型
                        //2010-07-22 经过测试 是string类型
                        case MySqlDataTypeCollection.TINYTEXT:
                        case MySqlDataTypeCollection.TEXT:
                        case MySqlDataTypeCollection.MEDIUMTEXT:
                        case MySqlDataTypeCollection.LONGTEXT:

                        case MySqlDataTypeCollection.ENUM:
                        case MySqlDataTypeCollection.SET:
                        case MySqlDataTypeCollection.CHAR:
                        case MySqlDataTypeCollection.VARCHAR:
                        default:
                            column.DataType = new DataTypeSchema(DotNetDataType.String.ToString(), column.DataType.Length, -1, -1, column.DataType.IsAutoIncrement);
                            break;

                    }
                }
            }
        }

        void ISchemaAspect.Transformation(DatabaseSchema database)
        {
            //要判断一些错误

            //1主键不能是TEXT，改成VERCHAR

            database.Info = new DatabaseInfo();

            database.Info.DatabaseType = DatabaseType.MySql;

            foreach (TableSchema table in database.Tables)
            {
                foreach (ColumnSchema column in table.Columns)
                {
                    DotNetDataType type = Pixysoft.EnumCoder.ToEnum<DotNetDataType>(column.DataType.DataType);

                    switch (type)
                    {
                        case DotNetDataType.Int32:
                            column.DataType = new DataTypeSchema(MySqlDataTypeCollection.INT.ToString(), -1, 255, -1, column.DataType.IsAutoIncrement);
                            break;
                        case DotNetDataType.Int64:
                            column.DataType = new DataTypeSchema(MySqlDataTypeCollection.BIGINT.ToString(), -1, 255, -1, column.DataType.IsAutoIncrement);
                            break;

                        case DotNetDataType.Decimal:
                        case DotNetDataType.Single:
                        case DotNetDataType.Double:
                            column.DataType = new DataTypeSchema(MySqlDataTypeCollection.DOUBLE.ToString(), -1,
                                column.DataType.Precision > 53 ? 53 : column.DataType.Precision,
                                column.DataType.Scale > 30 ? 30 : column.DataType.Scale, column.DataType.IsAutoIncrement);
                            break;

                        case DotNetDataType.DateTime:
                            column.DataType = new DataTypeSchema(MySqlDataTypeCollection.DATETIME.ToString(), -1, -1, -1, column.DataType.IsAutoIncrement);
                            break;

                        case DotNetDataType.Char:
                            column.DataType = new DataTypeSchema(MySqlDataTypeCollection.BIT.ToString(), -1,
                                column.DataType.Precision > 64 ? 64 : column.DataType.Precision,
                                -1, column.DataType.IsAutoIncrement);
                            break;

                        case DotNetDataType.Byte:
                            column.DataType = new DataTypeSchema(MySqlDataTypeCollection.LONGBLOB.ToString(), -1, -1, -1, column.DataType.IsAutoIncrement);//09-09-24 用最大值
                            break;

                        case DotNetDataType.Boolean:
                            column.DataType = new DataTypeSchema(MySqlDataTypeCollection.BIT.ToString(), -1,
                                1,
                                -1, column.DataType.IsAutoIncrement);
                            break;

                        case DotNetDataType.String:
                        default:
                            if (column.DataType.Length <= 0)
                            {
                                //09-09-23 Specified key was too long; max key length is 767 bytes

                                //09-09-23 BLOB/TEXT column 'TIMESTAMPCODE' used in key specification without a key length 

                                if (database.IsPrimaryKey(column) ||
                                    database.IsUnique(column) ||
                                    database.IsIndex(column))
                                {
                                    //09-10-12 Specified key was too long; max key length is 766 bytes

                                    column.DataType = new DataTypeSchema(MySqlDataTypeCollection.VARCHAR.ToString(), default_textlength, -1, -1, column.DataType.IsAutoIncrement);
                                }
                                else
                                {
                                    column.DataType = new DataTypeSchema(MySqlDataTypeCollection.TEXT.ToString(),
                                       -1,
                                       -1,
                                       -1, column.DataType.IsAutoIncrement);
                                }
                            }
                            else
                            {
                                column.DataType = new DataTypeSchema(MySqlDataTypeCollection.VARCHAR.ToString(),
                                    column.DataType.Length > 59516 ? 59516 : column.DataType.Length,
                                    -1,
                                    -1, column.DataType.IsAutoIncrement);
                            }
                            break;
                    }
                }
            }
        }

        List<string> ISchemaAspect.GetDataTypeList()
        {
            return Pixysoft.EnumCoder.ToList<MySqlDataTypeCollection>();
        }

        List<string> ISchemaAspect.GetDataTypeRegexList()
        {
            List<string> list = new List<string>();
            list.Add(@"[\w]+\([\d]+\)[,]{0,1}");
            list.Add(@"[\w]+\([\d]+[,\d]*\)[,]{0,1}");

            foreach (string type in Pixysoft.EnumCoder.ToList<MySqlDataTypeCollection>())
                list.Add(type + "[,]{0,1}");

            return list;
        }

        public List<string> GetKeyWordsList()
        {
            return systemNameList;
        }

        bool ISchemaAspect.IsValidName(string name)
        {
            if (name == null)
                return false;

            if (name.Trim() == "")
                return false;

            if (systemNameList.Contains(name.ToUpper()))
                return false;

            if (name.Length > 30)
                return false;

            if (Regex.Match(name, "[a-z,A-Z]{1}[a-z,A-Z,1-9,_,$,#]*").Value != name)
                return false;

            return true;
        }

        string ISchemaAspect.ValidateDefaultValue(string input, DataTypeSchema datatype)
        {
            if (input == null)
                return null;

            MySqlDataTypeCollection datatypeEnum = Pixysoft.EnumCoder.ToEnum<MySqlDataTypeCollection>(datatype.DataType);

            switch (datatypeEnum)
            {
                case MySqlDataTypeCollection.YEAR:
                case MySqlDataTypeCollection.TINYINT:
                case MySqlDataTypeCollection.SMALLINT:
                case MySqlDataTypeCollection.MEDIUMINT:
                case MySqlDataTypeCollection.INT:
                case MySqlDataTypeCollection.INTEGER:
                case MySqlDataTypeCollection.BIGINT:
                    try
                    {
                        return Int64.Parse(input).ToString();
                    }
                    catch { return null; }

                case MySqlDataTypeCollection.BIT:
                    try
                    {
                        return Char.Parse(input).ToString();
                    }
                    catch { return null; }

                case MySqlDataTypeCollection.BOOL:
                case MySqlDataTypeCollection.BOOLEAN:
                    try
                    {
                        return Boolean.Parse(input).ToString();
                    }
                    catch { return null; }

                case MySqlDataTypeCollection.FLOAT:
                case MySqlDataTypeCollection.DOUBLE:
                case MySqlDataTypeCollection.DOUBLE_PRECISION:
                case MySqlDataTypeCollection.REAL:
                case MySqlDataTypeCollection.DECIMAL:
                case MySqlDataTypeCollection.DEC:
                case MySqlDataTypeCollection.FIXED:
                case MySqlDataTypeCollection.NUMERIC:
                    try
                    {
                        return Double.Parse(input).ToString();
                    }
                    catch { return null; }

                case MySqlDataTypeCollection.DATE:
                case MySqlDataTypeCollection.DATETIME:
                case MySqlDataTypeCollection.TIMESTAMP:
                case MySqlDataTypeCollection.TIME:
                    try
                    {
                        return DateTime.Parse(input).ToString();
                    }
                    catch { return null; }

                case MySqlDataTypeCollection.VARBINARY:
                case MySqlDataTypeCollection.BINARY:
                case MySqlDataTypeCollection.TINYBLOB:
                case MySqlDataTypeCollection.TINYTEXT:
                case MySqlDataTypeCollection.BLOB:
                case MySqlDataTypeCollection.TEXT:
                case MySqlDataTypeCollection.MEDIUMBLOB:
                case MySqlDataTypeCollection.MEDIUMTEXT:
                case MySqlDataTypeCollection.LONGBLOB:
                case MySqlDataTypeCollection.LONGTEXT:
                    return input;

                case MySqlDataTypeCollection.ENUM:
                case MySqlDataTypeCollection.SET:
                case MySqlDataTypeCollection.CHAR:
                case MySqlDataTypeCollection.VARCHAR:
                default:
                    try
                    {
                        return Double.Parse(input).ToString();
                    }
                    catch
                    {
                        return "'" + input.Replace("'", "") + "'";
                    }
            }
        }


        public ISqlBuilder SqlBuilder
        {
            get
            {
                return MySqlBuilder.Instance;
            }
        }

        public void CreateDatabase(ISchemaCommand command, string databasename)
        {
            command.SQL = SqlBuilder.CreateDatabase(databasename);

            command.Execute();
        }

        public void DropDatabase(ISchemaCommand command, string databasename)
        {
            command.SQL = SqlBuilder.DropDatabase(databasename);

            command.Execute();
        }
    }
}
